using System;
using System.Data;
using Firestarter.DB.Schema.Logic;
using Habanero.Base;
using Habanero.DB;
using System.Data.SqlClient;

namespace Firestarter.DB.Schema.Test.Logic
{
    public class TestUtilsDB_SqlServer : TestUtilsDB
    {
        public override DatabaseConfig CreateTestDatabaseConfig()
        {
            return new DatabaseConfig("SqlServer", "localhost", TestDatabaseName, "sa", "sa", null);
        }

        protected override DatabaseConfig CreateMasterDatabaseConfig()
        {
            return new DatabaseConfig("SqlServer", "localhost", "master", "sa", "sa", null);
        }

        public override CreateTableSqlBuilder CreateSqlBuilderCreateTable(string tableName)
        {
            return new CreateTableSqlBuilder_SqlServer(tableName);
        }

        public override void CreateTestDatabase()
        {

            try
            {

                var masterConnection = this.CreateMasterConnection();
                using (masterConnection)
                {
                    try
                    {
                        DropDatabase();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                    }
                    ExecuteSql(masterConnection, string.Format(@"CREATE DATABASE [{0}]", this.TestDatabaseName));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

        }



        public override void ClearDatabase(IDbConnection connection)
        {
            string sqlStatement = string.Format(@"
                        USE {0};
                                
                        declare @sql varchar(1024)
                        declare curs cursor for
                        select 'ALTER TABLE ['+tab.name+'] DROP CONSTRAINT ['+cons.name+']'
                        from sys.objects cons,sys.objects tab
                        where cons.type in ('C', 'F', 'PK', 'UQ', 'D')
                        and cons.parent_object_id=tab.object_id and tab.type='U'
                        order by cons.type

                        open curs
                        fetch next from curs into @sql
                        while (@@fetch_status = 0)
                        begin
                        exec(@sql)
                        fetch next from curs into @sql
                        end
                        close curs
                        deallocate curs;

                        -- sp_MSForEachTable 'DROP TABLE [?]';
                        -- Line above replaced with code below due to permissions issues.
                        declare @sqlDropTable varchar(1024)
                        declare cursDropTable cursor for
                        select 'DROP TABLE ['+tab.name+']'
                        from sys.objects tab
                        where tab.type='U'
                        order by tab.name

                        open cursDropTable
                        fetch next from cursDropTable into @sqlDropTable
                        while (@@fetch_status = 0)
                        begin
                        exec(@sqlDropTable)
                        fetch next from cursDropTable into @sqlDropTable
                        end
                        close cursDropTable
                        deallocate cursDropTable;
                        ", TestDatabaseName);
            ExecuteSql(connection, sqlStatement);
        }

        public override void DropDatabase()
        {
            var masterConnection = this.CreateMasterConnection();

            using (masterConnection)
            {
                SqlConnection.ClearAllPools();
                ExecuteSql(masterConnection, string.Format(@"Use [master]"));
                ExecuteSql(masterConnection,
                           string.Format(
                               @"IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name='{0}') DROP DATABASE [{0}]",
                               TestDatabaseName));
            }

        }

    }
}